package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;



import DomainLayer.Journal;
import DomainLayer.LabSchemeMeasurement;


public class DBLabScheme implements IFDBLabScheme{
	
	private Connection con;
	
	public DBLabScheme(){
		con = DbConnection1.getInstance().getDBcon();
		}

	@Override
	public int createScheme(LabSchemeMeasurement scheme) throws Exception {
		int rc = -1;
		String query = "INSERT INTO MeasurementsLabScheme  VALUES('" +
		        scheme.getName() + "', '" +
				scheme.getDate()+ "', '" +
				scheme.getLeukocytter() + "', '" +
				scheme.getThrombocytter() + "', '" +
				scheme.getKoagulationsfaktorer() + "', '" +
				scheme.getBase_excess() + "', '" +
				scheme.getOxygen() + "', " +
				scheme.getHydrogencarbonation()+  "," +
				scheme.getKalium() + ", '" +
				scheme.getNatrium()+ "', '" +
				scheme.getCreatininium() + "', '" +
				scheme.getCarbamid()+ "', '" +
				scheme.getAlbumin()+ "', '" +
				scheme.getCreaktivtprotein()+ "', '" +
				
				scheme.getGlucose0600() + "', '" +
				scheme.getGlucose1200() + "', '" +
				scheme.getGlucose1730() + "', '" +
				scheme.getGlucose2130() +"', '" +
				scheme.getHemoglobin()+"', '" +
				scheme.getPh()+"', " +
				scheme.getSyre_base_status()+
				")" ;
		
		System.out.println("insert : " + query);
		
		try{Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				rc = stmt.executeUpdate(query);
				stmt.close();
				}catch(SQLException ex){
					System.out.println("customer does not exist");
					throw new Exception ("insertion was not correct");
				}
		return rc;
	}

	@Override
	public int deleteScheme(String name) {
		int rc = -1;
		String query="DELETE FROM MeasurementsLabScheme WHERE name = '" +
				name + "'";
	         System.out.println(query);
		try{ // delete from employee
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
		  	rc = stmt.executeUpdate(query);
		  	stmt.close();
		}//slut try	
	        catch(Exception ex){
		  	System.out.println("Delete exception in customer db: "+ex);
	        }
		return rc;
	}

	@Override
	public LabSchemeMeasurement getScheme(String name) {
		String wClause = " name = '" + name + "'";
		return singleWhere(wClause);
	}
	
	
	
	private ArrayList<LabSchemeMeasurement> miscWhere(String wClause	) {
		ResultSet results;
		ArrayList<LabSchemeMeasurement> list = new ArrayList<LabSchemeMeasurement>();

		String query = buildQuery(wClause);
		System.out.println("Query= " + query);

		try { // read the employee from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				LabSchemeMeasurement labScheme = new LabSchemeMeasurement();
				labScheme = buildLabScheme(results);
				list.add(labScheme);
			}// end while
			stmt.close();

		}// slut try
		catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;

	}

	@Override
	public int updateScheme(LabSchemeMeasurement scheme) {
		
		LabSchemeMeasurement labObj  = scheme;
		int rc=-1;
	  
		String query="UPDATE DBLabScheme SET "+
		 	  "date ='"+ labObj.getDate() + "', " +
		 	  "leukocytter ='"+ labObj.getLeukocytter() + "', "+
		 	  "thrombocytter ='" + labObj.getThrombocytter() + ", "+
		 	  "koagulationsfaktorer ='" + labObj.getKoagulationsfaktorer() + "', "+
		 	  "base_excess ='" + labObj.getBase_excess() + ", "+
		 	  "oxygen ='" + labObj.getOxygen() + ", "+
		 	  "hydrogencarbonation ='" + labObj.getHydrogencarbonation() + ", "+
		 	  "kalium ='" + labObj.getKalium() + ", "+
		 	  "natrium ='" + labObj.getNatrium() + ", "+
		 	  "creatinium ='" + labObj.getCreatininium() + ", "+
		 	  "base_excess ='" + labObj.getCarbamid() + ", "+
		 	  "albumin ='" + labObj.getAlbumin() + ", "+
		 	  "creaktivprotein ='" + labObj.getCreaktivtprotein() + ", "+
		 	  "glucose_06_00_clock ='" + labObj.getGlucose0600() + ", "+
		 	  "glucose_12_00_clock ='" + labObj.getGlucose1200() + ", "+
		 	  "glucose_06_00_clock ='" + labObj.getGlucose0600() + ", "+
		 	  "glucose_17_30_clock ='" + labObj.getGlucose1730() + ", "+
		 	  "glucose_21_30_clock ='" + labObj.getGlucose2130() + ", "+
		          " WHERE name = '"+ labObj.getName() + "'";
                System.out.println("Update query:" + query);
  		try{ // update employee
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);
                      
	 	 	stmt.close();
		}//end try	
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in employee db: "+ex);
	  	}
		return(rc);
		
	}
	
	 private String buildQuery(String wClause)
	 {
	  String query="SELECT * FROM MeasurementsLabScheme";
	  
	  if (wClause.length()>0)
	   query=query+" WHERE "+ wClause;
	   
	  return query;
	 }
	 
	 private LabSchemeMeasurement singleWhere(String wClause)
	  {
	   ResultSet results;
	   LabSchemeMeasurement labObj = new LabSchemeMeasurement();

	   	String query =  buildQuery(wClause);
	                 System.out.println(query);
	   try{ // read the employee from the database
	     Statement stmt = con.createStatement();
	     stmt.setQueryTimeout(5);
	     results = stmt.executeQuery(query);
	     
	     if(results.next() ){
	                             labObj = buildLabScheme(results);
	                             //association is to be build
	                            
	                             stmt.close();

	     			}
	                         else{ //no customer was found
	                             labObj = null;
	                         }
	   }//end try 
	    catch(Exception e){
	     System.out.println("Query exception: "+e);
	    }
	   return labObj;
	  }
	 
	 private LabSchemeMeasurement buildLabScheme(ResultSet results)
     {   LabSchemeMeasurement labObj = new LabSchemeMeasurement("");
         try{ // the columns from the table emplayee  are used
        	   labObj.setName(results.getString("name"));
        	   labObj.setDate(results.getString("date"));
        	   labObj.setHemoglobin(results.getString("hemoglobin"));
        	   labObj.setLeukocytter(results.getString("Leukocytter"));
        	   labObj.setKoagulationsfaktorer(results.getString("koagulationsfaktorer"));
        	   labObj.setThrombocytter(results.getString("thrombocytter"));
        	   labObj.setBase_excess(results.getString("base_excess"));
        	   labObj.setOxygen(results.getString("oxygen"));
        	   labObj.setHydrogencarbonation(results.getString("hydrogencarbonation"));
        	   labObj.setKalium(results.getString("kalium"));
        	   labObj.setNatrium(results.getString("natrium"));
        	   labObj.setCreatininium(results.getString("creatininium"));
        	   labObj.setCarbamid(results.getString("carbamid"));
        	   labObj.setAlbumin(results.getString("albumin"));
        	   labObj.setCreaktivtprotein(results.getString("creaktivtprotein"));
        	   labObj.setGlucose0600(results.getString("glucose_06_00_clock"));
        	   labObj.setGlucose1200(results.getString("glucose_12_00_clock"));
        	   labObj.setGlucose1730(results.getString("glucose_17_30_clock"));
        	   labObj.setGlucose2130(results.getString("glucose_21_30_clock"));
        	   labObj.setPh(results.getString(18));
        	   labObj.setSyre_base_status(results.getString(19));
               
                 }
        catch(Exception e)
        {
            System.out.println("error in building the scheme object");
        }
        return labObj;
     }
	 
	 
	 public ArrayList<LabSchemeMeasurement> getAllLabSchemeMeasurements(String name)
	 {
		 String wClause = " name = '" + name + "'";
		 return miscWhere(wClause);
		 
	 }
	 
	 public ArrayList<LabSchemeMeasurement> getAllLabSchemeMeasurements()
	 {
		 String wClause = ""; 
		return miscWhere(wClause);
	 }
	 
	 
	 public ArrayList<LabSchemeMeasurement> getLabSchemeForJournal(Journal journal){
		 ResultSet results;
		 ArrayList<LabSchemeMeasurement> list = new ArrayList<LabSchemeMeasurement>();
		
		 String query="SELECT * FROM MeasurementsLabScheme where name=(select top 1 nameLabScheme From LabSchemeJournal WHERE NameJournal='" +journal.getName()+"'"
				 + "and contactCauses='" +journal.getContact_causes()+ "')";
		 System.out.println("Query getLabSchemeForJournal:" + query);
		 
		 try { // read the employee from the database
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				results = stmt.executeQuery(query);

				while (results.next()) {
					LabSchemeMeasurement labScheme = new LabSchemeMeasurement();
					labScheme = buildLabScheme(results);
					list.add(labScheme);
				}// end while
				stmt.close();

			}// slut try
			catch (Exception e) {
				System.out.println("Query exception - select: " + e);
				e.printStackTrace();
			}
			return list;
		
		 
		  
		  
		   
		   
		  
		 
		 
	 }
	 
	 

}
